﻿package c.w.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import c.w.base.dto.Car;
import c.w.base.dto.CarProductNumber;
import c.w.base.dto.Person;
import c.w.base.dto.PersonProductNumber;

public class ConnDB {
	
	/*******************************************公共变量********************************************/
	Connection conn = null; 
	ResultSet rs =null;
	PreparedStatement ps = null;
	
	
	/*******************************************添加用户********************************************/
	
	/**
	 * 添加一个车载用户
	 * @author PHY
	 * @param c_city  车载用户所在城市
	 * @param c_id  车载用户id,主键
	 * @param c_password 密码
	 * @param taxi_company  出租车公司
	 * @param car_number  车牌号
	 * @param name  司机姓名
	 * @param cellphone_number  手机号
	 * @param permit_drive_credentials_number  准驾证号
	 * @param friendly_score  友好积分
	 * @param integrity_score  诚信积分
	 * @param integrity_id  外键——与诚信表关联，是诚信表id：主键
	 * @return true:添加成功		false：添加失败
	 */
	public boolean addCar(String c_city, int c_id, String c_password, String taxi_company, String car_number, String name, 
						  String cellphone_number, String permit_drive_credentials_number,long friendly_score,
						  long integrity_score, int integrity_id){
		String c_information_city = "c_information_"+c_city;
		String password = "c_"+c_password;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/persons_and_cars?useUnicode=true&characterEncoding=UTF-8","root","root");
		    ps = conn.prepareStatement("insert into "+c_information_city+"(c_id,c_password,taxi_company,car_number,name,cellphone_number,permit_drive_credentials_number,friendly_score,integrity_score,integrity_id) values(?,?,?,?,?,?,?,?,?,?)");
			ps.setInt(1,c_id);
			ps.setString(2, password);
			ps.setString(3, taxi_company);
			ps.setString(4, car_number);
			ps.setString(5, name);
			ps.setString(6, cellphone_number);
			ps.setString(7, permit_drive_credentials_number);
			ps.setLong(8, friendly_score);
			ps.setLong(9, integrity_score);
			ps.setInt(10, integrity_id);
					
			ps.executeUpdate();
		}
		catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
		finally{
			try{
				conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return true;
	}
	
	/**
	 * 添加一个打车用户
	 * @author PHY
	 * @param p_city  打车用户所在城市
	 * @param p_id  打车用户id,主键
	 * @param p_password 密码
	 * @param cellphone_number  手机号
	 * @param consumption_score  消费积分
	 * @param integrity_score  诚信积分
	 * @param integrity_id  外键——与诚信表关联，是诚信表id：主键
	 * @return true：添加成功	false：添加失败
	 */
	public boolean addPerson(String p_city, int p_id, String p_password, String cellphone_number, long consumption_score, long integrity_score, int integrity_id){
		String p_information_city = "p_information_"+p_city;
		String password = "p_"+p_password;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/persons_and_cars?useUnicode=true&characterEncoding=UTF-8","root","root");
		    ps = conn.prepareStatement("insert into "+p_information_city+"(p_id,p_password,cellphone_number,consumption_score,integrity_score,integrity_id) values(?,?,?,?,?)");
			ps.setInt(1,p_id);
			ps.setString(2, password);
			ps.setString(3, cellphone_number);
			ps.setLong(4, consumption_score);
			ps.setLong(5, integrity_score);
			ps.setInt(6, integrity_id);
				
			ps.executeUpdate();
		}
		catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
		finally{
			try{
				conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return true;
	}
	
	/*********************************************添加用户诚信度******************************************/
	
	/**
	 * 添加一个车载用户诚信度
	 * @author PHY
	 * @param c_city  车载用户所在城市
	 * @param integrity_id  主键,与用户信息表关联
	 * @param integrity  诚信度
	 * @param exposed_times  被举报次数
	 * @return true：添加成功	false：添加失败
	 */
	public boolean addCarIntegrity(String c_city, int integrity_id){
		String c_integrity_city = "c_integrity_"+c_city;
		
		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/persons_and_cars?useUnicode=true&characterEncoding=UTF-8","root","root");
		    ps = conn.prepareStatement("insert into "+c_integrity_city+"(integrity_id,integrity,exposed_times) values(?,?,?)");
		    ps.setInt(1,integrity_id);
		    ps.setLong(2, 0);
			ps.setInt(3, 0);
			
			ps.executeUpdate();
		}
		catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
		finally{
			try{
				conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return true;
	}
	
	/**
	 * 添加一个打车用户诚信度
	 * @author PHY
	 * @param p_city  打车用户所在城市
	 * @param integrity_id  主键,与用户信息表关联
	 * @param integrity  诚信度
	 * @param exposed_times  被举报次数
	 * @return true：添加成功	false：添加失败
	 */
	public boolean addPersonIntegrity(String p_city, int integrity_id){
		String p_integrity_city = "p_integrity_"+p_city;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/persons_and_cars?useUnicode=true&characterEncoding=UTF-8","root","root");
		    ps = conn.prepareStatement("insert into "+p_integrity_city+"(integrity_id,integrity,exposed_times) values(?,?,?)");
		    ps.setInt(1,integrity_id);
		    ps.setLong(2, 0);
			ps.setInt(3, 0);
			
			ps.executeUpdate();
		}
		catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
		finally{
			try{
				conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return true;
	}
	
	
	/*******************************************添加用户数据统计***********************************************/
	
	/**
	 * 添加一个车载用户的数据统计
	 * @author PHY
	 * @param c_city  车所在城市
	 * @param c_id  车id,主键
	 * @param active_time  激活时间
	 * @param login_time  登录时间
	 * @param login_city  登录城市
	 * @param login_IP  登录IP
	 * @return true：添加成功	false：添加失败
	 */
	public boolean addCarDataCount(String c_city, int c_id, String active_time, String login_time, String login_city, String login_IP){
		String c_datacount_city = "c_datacount_"+c_city;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/persons_and_cars?useUnicode=true&characterEncoding=UTF-8","root","root");
		    ps = conn.prepareStatement("insert into "+c_datacount_city+"(c_id,active_time,login_time,login_city,login_IP) values(?,?,?,?,?)");
			ps.setInt(1,c_id);
			ps.setString(2, active_time);
			ps.setString(3, login_time);
			ps.setString(4, login_city);
			ps.setString(5, login_IP);
				
			ps.executeUpdate();
		}
		catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
		finally{
			try{
				conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return true;
	}
	
	/**
	 * 添加一个打车用户的数据统计
	 * @author PHY
	 * @param p_city  人所在城市
	 * @param p_id  人id,主键
	 * @param active_time  激活时间
	 * @param login_time  登录时间
	 * @param login_city  登录城市
	 * @param login_IP  登录IP
	 * @param share_car_times  拼车次数
	 * @return true：添加成功	false：添加失败
	 */
	public boolean addPersonDataCount(String p_city, int p_id, String active_time, String login_time, String login_city, String login_IP, long share_car_times){
		String p_datacount_city = "p_datacount_"+p_city;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/persons_and_cars?useUnicode=true&characterEncoding=UTF-8","root","root");
		    ps = conn.prepareStatement("insert into "+p_datacount_city+"(p_id,active_time,login_time,login_city,login_IP,share_car_times) values(?,?,?,?,?,?)");
			ps.setInt(1,p_id);
			ps.setString(2, active_time);
			ps.setString(3, login_time);
			ps.setString(4, login_city);
			ps.setString(5, login_IP);
			ps.setLong(6, share_car_times);
			
			ps.executeUpdate();
		}
		catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
		finally{
			try{
				conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return true;
	}
	
	
	/*******************************************更新用户数据统计***********************************************/
	
	/**
	 * 更新车载用户数据统计
	 * @author PHY
	 * @param c_city 车载用户所在城市
	 * @param c_id 车载用户id
	 * @param login_time 登录时间
	 * @param login_city 登录城市
	 * @param login_IP 登录IP
	 * @return
	 */
	public boolean updateCarDatacount(String c_city, int c_id, String login_time, String login_city, String login_IP){
		String c_datacount_city = "c_datacount_"+c_city;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/persons_and_cars?useUnicode=true&characterEncoding=UTF-8","root","root");
		    ps = conn.prepareStatement("update "+c_datacount_city+" set login_time=?,login_city=?,login_IP=? where c_id=?");
		    ps.setString(1, login_time);
		    ps.setString(2, login_city);
		    ps.setString(3, login_IP);
		    ps.setInt(4, c_id);
			
			ps.executeUpdate();
		}
		catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
		finally{
			try{
				conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return true;
	}
	
	/**
	 * 更新车载用户数据统计
	 * @author PHY
	 * @param c_city 车载用户所在城市
	 * @param c_id 车载用户id
	 * @param login_time 登录时间
	 * @param login_city 登录城市
	 * @param login_IP 登录IP
	 * @param share_car_times  拼车次数
	 * @return
	 */
	public boolean updatePersonDatacount(String p_city, int p_id, String login_time, String login_city, String login_IP, long share_car_times){
		String p_datacount_city = "p_datacount_"+p_city;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/persons_and_cars?useUnicode=true&characterEncoding=UTF-8","root","root");
		    ps = conn.prepareStatement("update "+p_datacount_city+" set login_time=?,login_city=?,login_IP=?,share_car_times=? where p_id=?");
		    ps.setString(1, login_time);
		    ps.setString(2, login_city);
		    ps.setString(3, login_IP);
		    ps.setLong(4, share_car_times);
		    ps.setInt(5, p_id);
			
			ps.executeUpdate();
		}
		catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
		finally{
			try{
				conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return true;
	}
	
	
	/****************************************更新用户积分****************************************/
	
	/**更新一个车载用户的积分
	 * @author PHY
	 * @param c_city  车载用户所在城市
	 * @param c_id  车载用户id,主键
	 * @param friendly_score  友好积分
	 * @param integrity_score  诚信积分
	 * @return true：更新成功	false：更新失败
	 */
	public boolean updateCarFriendlyAndIntegrityScore(String c_city, int c_id, long friendly_score, long integrity_score){
		String c_information_city = "c_information_"+c_city;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/persons_and_cars?useUnicode=true&characterEncoding=UTF-8","root","root");
		    ps = conn.prepareStatement("update "+c_information_city+" set friendly_score=?,integrity_score=? where c_id=?");
			ps.setLong(1, friendly_score);
			ps.setLong(2, integrity_score);
			ps.setInt(3,c_id);
			
			ps.executeUpdate();
		}
		catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
		finally{
			try{
				conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return true;
	}
	
	/**更新一个打车用户的积分
	 * @author PHY
	 * @param p_city  打车用户所在城市
	 * @param p_id  打车用户id,主键
	 * @param consumption_score  消费积分
	 * @param integrity_score  诚信积分
	 * @return true：更新成功	false：更新失败
	 */
	public boolean updatePersonConsumption_scoreAndIntegrityScore(String p_city, int p_id, long consumption_score, long integrity_score){
		String p_information_city = "p_information_"+p_city;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/persons_and_cars?useUnicode=true&characterEncoding=UTF-8","root","root");
		    ps = conn.prepareStatement("update "+p_information_city+" set consumption_score=?,integrity_score=? where p_id=?");
			ps.setLong(1, consumption_score);
			ps.setLong(2, integrity_score);
			ps.setInt(3,p_id);
			
			ps.executeUpdate();
		}
		catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
		finally{
			try{
				conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return true;
	}
	
	
	/******************************************更新用户诚信度***********************************************/
	
	/**更新一个车载用户诚信度
	 * @author PHY
	 * @param c_city  车载用户所在城市
	 * @param integrity_id  主键,与用户信息表关联
	 * @param integrity  诚信度
	 * @param exposed_times  被举报次数
	 * @return true：更新成功	false：更新失败
	 */
	public boolean updateCarIntegrity(String c_city, int integrity_id, long integrity, int exposed_times){
		String c_integrity_city = "c_integrity_"+c_city;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/persons_and_cars?useUnicode=true&characterEncoding=UTF-8","root","root");
		    ps = conn.prepareStatement("update "+c_integrity_city+" set integrity=?,exposed_times=? where integrity_id=?");
			ps.setLong(1, integrity);
			ps.setInt(2, exposed_times);
			ps.setInt(3,integrity_id);
			
			ps.executeUpdate();
		}
		catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
		finally{
			try{
				conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return true;
	}
	
	/**更新一个打车用户诚信度
	 * @author PHY
	 * @param p_city  打车用户所在城市
	 * @param integrity_id  主键,与用户信息表关联
	 * @param integrity  诚信度
	 * @param exposed_times  被举报次数
	 * @return true：更新成功	false：更新失败
	 */
	public boolean updatePersonIntegrity(String p_city, int integrity_id, long integrity, int exposed_times){
		String p_integrity_city = "p_integrity_"+p_city;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/persons_and_cars?useUnicode=true&characterEncoding=UTF-8","root","root");
		    ps = conn.prepareStatement("update "+p_integrity_city+" set integrity=?,exposed_times=? where integrity_id=?");
			ps.setLong(1, integrity);
			ps.setInt(2, exposed_times);
			ps.setInt(3,integrity_id);
			
			ps.executeUpdate();
		}
		catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
		finally{
			try{
				conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return true;
	}
	
	
	/********************************************得到用户的所有信息***************************************************/
	
	/**得到一个车载用户的所有信息
	 * @author PHY
	 * @param c_city  车在用户所在城市
	 * @param c_id  车在用户id,主键
	 * @param c_password 密码
	 * @return result：车载用户信息结果集		null:空结果集
	 */
	public ArrayList<Car> getCarAllInformation(String c_city, int c_id, String c_password){
		ArrayList<Car> result = new ArrayList<Car>();
		String c_information_city = "c_information_"+c_city;
		String c_integrity_city = "c_integrity_"+c_city;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn =  DriverManager.getConnection("jdbc:mysql://localhost:3306/persons_and_cars?useUnicode=true&characterEncoding=UTF-8","root","root");
		    ps = conn.prepareStatement("select info.taxi_company,info.car_number,info.name,info.cellphone_number,info.permit_drive_credentials_number,info.friendly_score,info.integrity_score,info.integrity_id,inte.integrity,inte.exposed_times from "+c_information_city+" info,"+c_integrity_city+" inte where info.c_id="+c_id+" and info.integrity_id = inte.integrity_id and info.c_password="+c_password);
		    rs = ps.executeQuery();
		   
		    while(rs.next()){
		    	Car c = new Car();
		    	c.setC_id(c_id);
		    	c.setTaxi_company(rs.getString("taxi_company"));
		    	c.setCar_number(rs.getString("car_number"));
		    	c.setName(rs.getString("name"));
		    	c.setCellphone_number(rs.getString("cellphone_number"));
		    	c.setPermit_drive_credentials_number(rs.getString("permit_drive_credentials_number"));
		    	c.setFriendly_score(rs.getLong("friendly_score"));
		    	c.setIntegrity_score(rs.getLong("integrity_score"));
		    	c.setIntegrity_id(rs.getInt("integrity_id"));
		    	c.setIntegrity(rs.getLong("integrity"));
		    	c.setExposed_times(rs.getInt("exposed_times"));
		    	result.add(c);
		    }
		}
		catch(Exception ex){
			ex.printStackTrace();
			return null;
		}
		finally{
			try{
				conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return result;
	}
	
	/**得到一个打车用户的所有信息
	 * @author PHY
	 * @param p_city  打车用户所在城市
	 * @param p_id	打车用户id,主键
	 * @param p_password 密码
	 * @return result：打车用户信息结果集		null：空结果集
	 */
	public ArrayList<Person> getPersonAllInformation(String p_city, int p_id, String p_password){
		ArrayList<Person> result = new ArrayList<Person>();
		String p_information_city = "p_information_"+p_city;
		String p_integrity_city = "p_integrity_"+p_city;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn =  DriverManager.getConnection("jdbc:mysql://localhost:3306/persons_and_cars?useUnicode=true&characterEncoding=UTF-8","root","root");
		    ps = conn.prepareStatement("select info.cellphone_number,info.consumption_score,info.integrity_score,info.integrity_id,inte.integrity,inte.exposed_times from "+p_information_city+" info,"+p_integrity_city+" inte where info.p_id="+p_id+" and info.integrity_id=inte.integrity_id and info.p_password="+p_password);
		    rs = ps.executeQuery();
		   
		    while(rs.next()){
		    	Person p = new Person();
		    	p.setP_id(p_id);
		    	p.setCellphone_number(rs.getString("cellphone_number"));
		    	p.setConsumption_score(rs.getLong("consumption_score"));
		    	p.setIntegrity_score(rs.getLong("integrity_score"));
		    	p.setIntegrity_id(rs.getInt("integrity_id"));
		    	p.setIntegrity(rs.getLong("integrity"));
		    	p.setExposed_times(rs.getInt("exposed_times"));
		    	result.add(p);
		    }
		}
		catch(Exception ex){
			ex.printStackTrace();
			return null;
		}
		finally{
			try{
				conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return result;
	}
	
	/********************************************得到产品编号及版本***********************************************/
	
	/**得到车载用户的产品编号及版本
	 * @author PHY
	 * @param id  车载用户产品编号表id,主键
	 * @return result：车载用户产品编号信息结果集		null：空结果集
	 */
	public ArrayList<CarProductNumber> getCarProductNumber(int id){
		ArrayList<CarProductNumber> result = new ArrayList<CarProductNumber>();
		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn =  DriverManager.getConnection("jdbc:mysql://localhost:3306/persons_and_cars?useUnicode=true&characterEncoding=UTF-8","root","root");
		    ps = conn.prepareStatement("select * from car_product_number");
		    rs = ps.executeQuery();
		   
		    while(rs.next()){
		    	CarProductNumber cpn = new CarProductNumber();
		    	cpn.setId(id);
		    	cpn.setProduct_number(rs.getString("product_number"));
		    	cpn.setVersion_number(rs.getString("version_number"));
		    	
		    	result.add(cpn);
		    }
		}
		catch(Exception ex){
			ex.printStackTrace();
			return null;
		}
		finally{
			try{
				conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return result;
	}
	
	/**得到打车用户的产品编号及版本
	 * @author PHY
	 * @param id  打车用户产品编号表id,主键
	 * @return result：打车用户产品编号信息结果集		null：空结果集
	 */
	public ArrayList<PersonProductNumber> getPersonProductNumber(int id){
		ArrayList<PersonProductNumber> result = new ArrayList<PersonProductNumber>();
		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn =  DriverManager.getConnection("jdbc:mysql://localhost:3306/persons_and_cars?useUnicode=true&characterEncoding=UTF-8","root","root");
		    ps = conn.prepareStatement("select * from person_product_number");
		    rs = ps.executeQuery();
		   
		    while(rs.next()){
		    	PersonProductNumber ppn = new PersonProductNumber();
		    	ppn.setId(id);
		    	ppn.setProduct_number(rs.getString("product_number"));
		    	ppn.setVersion_number(rs.getString("version_number"));
		    	
		    	result.add(ppn);
		    }
		}
		catch(Exception ex){
			ex.printStackTrace();
			return null;
		}
		finally{
			try{
				conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return result;
	}
}
